/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataLayer;

import Entities.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Vector;
import java.util.*;
//import probadb.PostCard;
/**
 *
 * @author itabas
 */
public class DataAccess  {

    private Connection connection;
    // ссилки на підготовлені записи

    public PreparedStatement sqlGetProductById;
    
    public PreparedStatement sqlUpdateProduct;

    public PreparedStatement sqlCloseAuction;

    public PreparedStatement sqlCreateNewProduct;
    
    public PreparedStatement sqlallproducts;
    
    public PreparedStatement sqlGetUserByLogin;
    
    public PreparedStatement sqlGetUserById;
    
    //конструктор
    public DataAccess() throws Exception{
        //зєднання з базою
        connect();

        sqlGetProductById=connection.prepareStatement(
                "SELECT * FROM NewProduct WHERE Id = ? "
                );
        
        sqlUpdateProduct=connection.prepareStatement(
                "UPDATE newProduct SET  CurrentUser = ?, CurrentPrice = ?  "+
                "WHERE Id =  ?");
        
        sqlCloseAuction=connection.prepareStatement(
                "UPDATE newProduct SET  IsExpired = ?  "+
                "WHERE Id =  ?");
        
        sqlCreateNewProduct=connection.prepareStatement(
                "INSERT INTO newProduct (Name, Description, DayOfAuction,"+
                "CurrentUser, CurrentPrice, MinStep)"+
                "VALUES (?, ?, ?, ?, ?, ?)");
         
        sqlallproducts=connection.prepareStatement("SELECT * FROM NewProduct WHERE IsExpired=0");
        
        sqlGetUserByLogin=connection.prepareStatement("SELECT * FROM Users WHERE Login= ?");
        
        sqlGetUserById=connection.prepareStatement("SELECT * FROM Users WHERE ID= ?");
    }

    public Connection connect() throws Exception{
        String driver="sun.jdbc.odbc.JdbcOdbcDriver";
        String url="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\\Auction.mdb;}";
        Class.forName(driver);
        connection = DriverManager.getConnection(url);
        return connection;
        //connection.setAutoCommit(false);
    }
    
    public boolean updateProduct(int productId, int currentUser, int currentPrice){
        try{
            int result;
        sqlUpdateProduct.setInt(1, currentUser);
        sqlUpdateProduct.setInt(2, currentPrice);
        sqlUpdateProduct.setInt(3, productId);

        result=sqlUpdateProduct.executeUpdate();
        if (result==0){
            connection.rollback();
            return false;
        }
        connection.commit();
        return true;
        }
        catch(SQLException sqlexception)
        {
            return false;
        }
    }

    public Product getProductById(String productId){
        try{
            sqlGetProductById.setString(1, productId);
            ResultSet resultSet=sqlGetProductById.executeQuery();
            if (!resultSet.next())
               return null;
            Product product =new Product();
            product.id=resultSet.getInt(1);
            product.name=resultSet.getString(2);
            product.description=resultSet.getString(3);
            product.dayOfAuction=resultSet.getString(4);
           int ii=resultSet.getInt(5);
                
                if( ii== 0)
                { product.currentUser="None";}
                 else
                {
                    product.currentUser = getUserById(ii).Name;
                }
            product.currentPrice=resultSet.getInt(6);
            product.minStep=resultSet.getInt(7);
            product.isExpired=resultSet.getBoolean(8);            
            return product;
        }
        catch(SQLException sqlexception)
        {
           return null;
        }
    }
 
    public boolean createNewProduct(String name, String description, String dayOfAuction,
                   int currentPrice, int minStep  ) {

        try{
        int result;
        sqlCreateNewProduct.setString(1,name );
        sqlCreateNewProduct.setString(2, description);
        sqlCreateNewProduct.setString(3, dayOfAuction);
        sqlCreateNewProduct.setInt(4, 0);
        sqlCreateNewProduct.setInt(5, currentPrice);
        sqlCreateNewProduct.setInt(6, minStep);

        result=sqlCreateNewProduct.executeUpdate();
        if (result==0){
            connection.rollback();
            return false;
        }
        connection.commit();
        return true;
        }
        catch(SQLException sqlexception ){
            return false;
        }
    }
    
    public boolean closeAuction(int productId){
        try{
            int result;
        sqlCloseAuction.setInt(1, 0);
        sqlCloseAuction.setInt(2, productId);

        result=sqlCloseAuction.executeUpdate();
        if (result==0){
            connection.rollback();
            return false;
        }
        connection.commit();
        return true;
        }
        catch(SQLException sqlexception)
        {
            return false;
        }
    }
    
    public List<Product> allProducts(){
         try{
            ResultSet resultSet=sqlallproducts.executeQuery();

            List<Product> tempList=new LinkedList<Product>();
            while (resultSet.next()){
                Product tempProduct=new Product();
                tempProduct.id=resultSet.getInt(1);
                tempProduct.name = resultSet.getString(2);
                tempProduct.description= resultSet.getString(3);
                tempProduct.dayOfAuction = resultSet.getString(4);
 
                int ii=resultSet.getInt(5);
                
                if( ii== 0)
                { tempProduct.currentUser="None";}
                 else
                {
                    tempProduct.currentUser = getUserById(ii).Name;
                }
                
                tempProduct.currentPrice = resultSet.getInt(6);
                tempProduct.minStep = resultSet.getInt(7);
                tempList.add(tempProduct);
            }
            return tempList;
        }
        catch(SQLException sqlexception){
           sqlexception.printStackTrace();
           return null;
        }
    }
    
    
    public User getUserByLogin(String login)
    {
         try{
            sqlGetUserByLogin.setString(1, login);
            ResultSet resultSet=sqlGetUserByLogin.executeQuery();
            if (!resultSet.next())
               return null;
            User user =new User();
            user.Id=resultSet.getInt(1);
            user.Login=resultSet.getString(2);
            user.Password=resultSet.getString(3);
            user.Name=resultSet.getString(4);

            return user;
        }
        catch(SQLException sqlexception)
        {
           return null;
        }
    }
    
      public User getUserById(int userId)
    {
         try{
            sqlGetUserById.setInt(1, userId);
            ResultSet resultSet=sqlGetUserById.executeQuery();
            if (!resultSet.next())
               return null;
            User user =new User();
            user.Id=resultSet.getInt(1);
            user.Login=resultSet.getString(2);
            user.Password=resultSet.getString(3);
            user.Name=resultSet.getString(4);

            return user;
        }
        catch(SQLException sqlexception)
        {
           return null;
        }
    }
    public void close(){
        try{
            sqlGetProductById.close();
            
            connection.close();
        }
        catch (SQLException sqlexception)
        {
            sqlexception.printStackTrace();
        }
    }
    
    protected void finalize(){
        close();
    }
}

